package com.clstu.batch;

import com.clstu.utils.JDBCUtils;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 这是一个测试jdbc 批处理性能的演示(PrepareStatement特有的)
 */
public class Batch_ {
    public static void main(String[] args) {

    }
    @Test//传统方式插入5000条记录测试用时
    public void method1() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into adm values (null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {//
            preparedStatement.setString(1,"tom"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.executeUpdate();//执行
        }
        long end = System.currentTimeMillis();
        System.out.println("传统插入耗时:===="+(end-start)+"ms");//3523ms
    }

    @Test//批处理方式插入5000条记录测试用时  --- (要在properties 的配置文件中配好才会生效!!)
    public void method2() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into adm values (null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 5000; i++) {//
            preparedStatement.setString(1,"tom"+i);
            preparedStatement.setString(2,"666");
            preparedStatement.addBatch();//加入到批处理包中
            /*
              底层源码是通过arrayList集合存储的
              //sql语句存放在 Object[]中, 具体是batchedArgs.elementData(是一个数组,1.5倍增长式的扩容)
               public void addBatch() throws SQLException {
                    synchronized(this.checkClosed().getConnectionMutex()) {
                        if (this.batchedArgs == null) {
                            this.batchedArgs = new ArrayList();//是一个集合
                        }

                        for(int i = 0; i < this.parameterValues.length; ++i) {
                            this.checkAllParametersSet(this.parameterValues[i], this.parameterStreams[i], i);
                        }

                        this.batchedArgs.add(new PreparedStatement.BatchParams(this.parameterValues, this.parameterStreams, this.isStream, this.streamLengths, this.isNull));
                    }
                }
             */
            if((i+1)%1000==0){//满1000条执行一次
                preparedStatement.executeBatch();//执行
                preparedStatement.clearBatch();//清空包
            }
        }
        long end = System.currentTimeMillis();
        /*
           往往会和 PrepareStatement 配合使用
           快的原因就在于 减少了编译的次数,减少了执行的次数,减少了网络传输的次数 !!!!!
           需要在properties文件的url后面加上 ?rewriteBatchedStatements=true 才会生效
         */
        System.out.println("批处理插入耗时:===="+(end-start)+"ms");//没配置properties(不生效):3492ms ------ 配置好后:64ms !!!!!
    }
}
